﻿using System;
using System.Data;
using System.Data.SqlClient;

using EFB.Lib.Entity;
using System.Text;

namespace EFB.Lib.Repository
{

    public class OperacaoLogRepository : BaseRepository
    {

        public void Incluir(OperacaoLogEntity OperacaoLog)
        {

            DataTable Table = null;
            StringBuilder sb = new StringBuilder();


            sb.Append("       INSERT INTO [EFB_MOVIMENTO] ");
            sb.Append("       ([MOVIMENTO_PAI_ID] "); //ok
            sb.Append("       ,[ITEMIDENTIF_ID] ");
            sb.Append("      ,[SKU_ID] ");
            sb.Append("       ,[QUANTIDADE] ");
            sb.Append("       ,[OPERACAO_ID] ");
            sb.Append("       ,[STATUS_MOV] ");
            sb.Append("       ,[OP_ID] ");
            sb.Append("       ,[DATA_MOV]) ");

            sb.Append("        VALUES ");

            sb.Append("        (null, ");
            sb.Append("        @rast, ");
            sb.Append("        @sku, ");
            sb.Append("       @qtd, ");
            sb.Append("       @Operacao, ");
            sb.Append("       0, ");
            sb.Append("                 null, ");
            sb.Append("       GETDATE()) ");

            Table = Sql.ExecuteReader("SELECT max([ITEMIDENTIF_ID])+1 as contador from [efabric].[dbo].[EFB_ITEMIDENTIF] ");


            string itemIden = Convert.ToString(Table.Rows[0]["contador"]);

            if (!OperacaoLog.Operacao.Equals("Producao", StringComparison.InvariantCultureIgnoreCase)) {

                if (OperacaoLog.Operacao.Equals("Saida", StringComparison.InvariantCultureIgnoreCase)) {
                    DataTable Table1 = null;
                    Table1 = Sql.ExecuteReader("SELECT SKU_ID  from [EFB_ITEMIDENTIF] WHERE [RASTREABILIDADE] = @rast ",
                                                new SqlParameter[] { 
                                                new SqlParameter("@rast", OperacaoLog.Rast) });

                    if (Table1 != null && Table1.Rows.Count > 0) {
                        string idSku = Convert.ToString(Table1.Rows[0]["SKU_ID"]);

                        Sql.ExecuteNonQuery(
                        sb.ToString(), new SqlParameter[] { 
                        new SqlParameter("@rast",  OperacaoLog.Rast),
                        new SqlParameter("@sku", idSku),
                        new SqlParameter("@qtd", OperacaoLog.Qtd), 
                        new SqlParameter("@Operacao", OperacaoLog.Operacao) 
                      //new SqlParameter("@op", OperacaoLog.Operacao)
            
                 });

                    } 
			else//caso nao existir rastreabilidade

                    {
                        throw new Exception("Rastreabilidade inexistente");
                    }

                } else {

                    Sql.ExecuteNonQuery(
                    sb.ToString(), new SqlParameter[] { 
                    new SqlParameter("@rast",  OperacaoLog.Rast),
                    new SqlParameter("@sku", OperacaoLog.Sku),
                    new SqlParameter("@qtd", OperacaoLog.Qtd), 
                    new SqlParameter("@Operacao", OperacaoLog.Operacao) 
                    //new SqlParameter("@op", OperacaoLog.Operacao)
            
                });
                }


            } else {

                Sql.ExecuteNonQuery(
                            sb.ToString(), new SqlParameter[] { 
                new SqlParameter("@rast", itemIden),
                new SqlParameter("@sku",  OperacaoLog.Op),
                new SqlParameter("@qtd", OperacaoLog.Qtd), 
                new SqlParameter("@Operacao", OperacaoLog.Operacao) 
                //new SqlParameter("@op", OperacaoLog.Operacao)
            
            });
            }


        }

        public Boolean VerificaExitenciItemIdent_Rastrebilidade(String Rast)
        {
            Boolean retorno = false;
            DataTable Table = Sql.ExecuteReader("SELECT * FROM EFB_ITEMIDENTIF WHERE RASTREABILIDADE = @rast",
                new SqlParameter[] { new SqlParameter("@rast", Rast) });
            if (Table != null && Table.Rows.Count > 0)
            {
                retorno = true;
            }
            return retorno;
        }

        public void IncluirItemIdentificacao(OperacaoLogEntity OperacaoLog)
        {

            if (!OperacaoLog.Operacao.Equals("Producao", StringComparison.InvariantCultureIgnoreCase))
            {

                if (OperacaoLog.Operacao.Equals("Saida", StringComparison.InvariantCultureIgnoreCase))
                {
                    DataTable Table1 = null;
                    Table1 = Sql.ExecuteReader("SELECT SKU_ID  from [EFB_ITEMIDENTIF] WHERE [RASTREABILIDADE] = @rast ",
                                                new SqlParameter[] { 
                                                new SqlParameter("@rast", OperacaoLog.Rast) });

                    if (Table1 != null && Table1.Rows.Count > 0)
                    {
                        string idSku = Convert.ToString(Table1.Rows[0]["SKU_ID"]);

                        Sql.ExecuteNonQuery(
                           "INSERT INTO [EFB_ITEMIDENTIF] " +
                           "       ( " +
                           "       [RASTREABILIDADE] " +
                           "       ,[SKU_ID] " +
                           "       ,[QTD_ORIGINAL] " +
                           "       ,[QTD_RESERVADA] " +
                           "       ,[QTD_BAIXADA]) " +
                           " VALUES " +
                           "       ( " +
                           "       @rast, " +
                           "       @sku, " +
                           "       @qtd, " +
                           "       null, " +
                           "       null " +
                           "      ) ",
                           new SqlParameter[] { 
                                    new SqlParameter("@rast", OperacaoLog.Rast),
                                    new SqlParameter("@sku", idSku),
                                    new SqlParameter("@qtd", OperacaoLog.Qtd)              
                                });



                    }
                    else
                    {
                        throw new Exception("Rastreabilidade Inexistente!");
                    }
                }

                else
                {

                    DataTable Table2 = null;
                    Table2 = Sql.ExecuteReader("SELECT SKU_ID  from [EFB_ITEMIDENTIF] WHERE [SKU_ID] = @sku ",
                                                new SqlParameter[] { 
                                                new SqlParameter("@sku", OperacaoLog.Sku) });

                    if (Table2 != null && Table2.Rows.Count > 0)
                    {


                        Sql.ExecuteNonQuery(
                   "INSERT INTO [EFB_ITEMIDENTIF] " +
                   "       ( " +
                   "       [RASTREABILIDADE] " +
                   "       ,[SKU_ID] " +
                   "       ,[QTD_ORIGINAL] " +
                   "       ,[QTD_RESERVADA] " +
                   "       ,[QTD_BAIXADA]) " +
                   " VALUES " +
                   "       ( " +
                   "       @rast, " +
                   "       @sku, " +
                   "       @qtd, " +
                   "       null, " +
                   "       null " +
                   "      ) ",
                   new SqlParameter[] { 
                            new SqlParameter("@rast", OperacaoLog.Rast),
                            new SqlParameter("@sku", OperacaoLog.Sku),
                            new SqlParameter("@qtd", OperacaoLog.Qtd)              
                        });
                    }

                    else
                    {
                        throw new Exception("Não existe Sku cadastrado!");

                    }
                }


            }
            else //
            {
                Sql.ExecuteNonQuery(
              "INSERT INTO [EFB_ITEMIDENTIF] " +
              "       ( " +
              "       [RASTREABILIDADE] " +
              "       ,[SKU_ID] " +
              "       ,[QTD_ORIGINAL] " +
              "       ,[QTD_RESERVADA] " +
              "       ,[QTD_BAIXADA]) " +
              " VALUES " +
              "       ( " +
              "       @rast, " +
              "       @sku, " +
              "       @qtd, " +
              "       null, " +
             "       null " +
              "      ) ", new SqlParameter[] { 
                        new SqlParameter("@rast", OperacaoLog.Rast),
                        new SqlParameter("@sku", OperacaoLog.Op),
                        new SqlParameter("@qtd", OperacaoLog.Qtd)              
            
                    });
            }

        }

        public void AtualizarQtdOriginal_ItemIdent_Por_Rastreab(OperacaoLogEntity OperacaoLog)
        {
            //modificar sql - insert

            //Sql.ExecuteNonQuery(
            //"UPDATE [EFB_ITEMIDENTIF] " +
            //"SET QTD_ORIGINAL =    " +
            //"(SELECT QTD_ORIGINAL " +
            //"FROM [EFB_ITEMIDENTIF] AS EFB_ITEMIDENTIF_1 " +
            //"WHERE (RASTREABILIDADE = @rast)) + @qtd " +
            //"WHERE (RASTREABILIDADE =  @rast) " 
            //, new SqlParameter[] { 
            //    new SqlParameter("@rast", OperacaoLog.Rast),
            //    new SqlParameter("@qtd", OperacaoLog.Qtd)              

            //});



            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO [EFB_ITEMIDENTIF](");
            query.Append("[RASTREABILIDADE],");
            query.Append("[SKU_ID],");
            query.Append("[QTD_ORIGINAL])");
            query.Append(" VALUES (");
            query.Append("@rast, ");
            query.Append("@sku,");
            query.Append("@qtd)");

            Sql.ExecuteNonQuery(query.ToString(),
                new SqlParameter[] { 
                new SqlParameter("@rast", OperacaoLog.Rast),
                new SqlParameter("@sku", OperacaoLog.Sku),
                new SqlParameter("@qtd", OperacaoLog.Qtd)
            });



        }

        public void Saida_ItemIdent(OperacaoLogEntity OperacaoLog)
        {


            DataTable Table = Sql.ExecuteReader("SELECT * FROM [efabric].[dbo].[EFB_ITEMIDENTIF] WHERE [RASTREABILIDADE] = @rast",
                new SqlParameter[] { new SqlParameter("@rast", OperacaoLog.Rast) });

            if (Table != null && Table.Rows.Count > 0)
            {
                StringBuilder sb = new StringBuilder();

                sb.Append("UPDATE [EFB_ITEMIDENTIF] SET");
                sb.Append("[QTD_ORIGINAL] = [QTD_ORIGINAL] - @qtd WHERE [RASTREABILIDADE] = @rast ");



                Sql.ExecuteNonQuery(sb.ToString(),
                    new SqlParameter[] { 
                    new SqlParameter("@qtd", OperacaoLog.Qtd),
                    new SqlParameter("@rast", OperacaoLog.Rast)

                });

            }
            else
            {
                throw new Exception("Rastreabilidade inexistente!");

            }





            //Sql.ExecuteNonQuery(
            //      "INSERT INTO [EFB_ITEMIDENTIF] " +
            //      "       ( " +
            //      "       [RASTREABILIDADE] " +
            //      "       ,[SKU_ID] " +
            //      "       ,[QTD_ORIGINAL] " +
            //      "       ,[QTD_RESERVADA] " +
            //      "       ,[QTD_BAIXADA]) " +
            //      " VALUES " +
            //      "       ( " +
            //      "       @rast, " +
            //      "       @sku, " +
            //      "       @qtd, " +
            //      "       null, " +
            //     "       null " +
            //      "      ) ", new SqlParameter[] { 
            //            new SqlParameter("@rast", OperacaoLog.Rast),
            //            new SqlParameter("@sku", OperacaoLog.Op),
            //            new SqlParameter("@qtd", OperacaoLog.Qtd)              

            //        });


            //      Sql.ExecuteNonQuery(
            //     "UPDATE [EFB_ITEMIDENTIF] " +
            //     "SET QTD_BAIXADA =    " +
            //     "(SELECT QTD_BAIXADA " +
            //     "FROM [EFB_ITEMIDENTIF] AS EFB_ITEMIDENTIF_1 " +
            //     "WHERE (RASTREABILIDADE = @rast)) + @qtd_movimento " +
            //     "WHERE (RASTREABILIDADE =  @rast) "
            //, new SqlParameter[] { 
            //          new SqlParameter("@rast", OperacaoLog.Rast), 
            //          new SqlParameter("@qtd_movimento", OperacaoLog.Qtd)              

            //      });

                 



        }

        public void cadastroProducao(OperacaoLogEntity OperacaoLog)
        {

            Object sku = Sql.ExecuteScalar("SELECT SKU_ID FROM EFB_ORDEMPROD WHERE OP_ID=@op",
                new SqlParameter[] { new SqlParameter("@op", OperacaoLog.Op) });

            Sql.ExecuteNonQuery(
            "INSERT INTO [EFB_ITEMIDENTIF] " +
            "       (" +
            "       [RASTREABILIDADE] " +
            "       ,[SKU_ID] " +
            "       ,[QTD_ORIGINAL] " +
            "       ,[QTD_RESERVADA] " +
            "       ,[QTD_BAIXADA]) " +
            " VALUES " +
            "       ( " +
            "       @rast, " +
            "       @sku, " +
            "       @qtd, " +
            "       0, " +
           "       0 " +
            "      ) ", new SqlParameter[] { 
                new SqlParameter("@rast", OperacaoLog.Rast),
                 new SqlParameter("@sku", Convert.ToString(sku)),
                new SqlParameter("@qtd", OperacaoLog.Qtd)              
            
            });//inserir o item novo no estoque com a rastreabilidade passada

            Object idOp = Sql.ExecuteScalar("SELECT @@IDENTITY ");

            DataTable Table = Sql.ExecuteReader("SELECT SKUENTRADA_ID,QUANTIDADE FROM EFB_SKUFORMOP WHERE OP_ID = @op",//consulta todo os item relacionado a op
                new SqlParameter[] { new SqlParameter("@op", OperacaoLog.Op) });
            if (Table != null && Table.Rows.Count > 0)
            {
                //inserir um item identificacao com a rastrabilidade
                foreach (DataRow Row in Table.Rows)
                {
                    String SKUENTRADA_ID = Convert.ToString(Row["SKUENTRADA_ID"]);
                    int qtd = Convert.ToInt16(Row["QUANTIDADE"]);
                    int qtdOp = Convert.ToInt16(OperacaoLog.Qtd);
                    int qtd_movimento = qtd * qtdOp;

                    Incluir_ConsumoComponente(SKUENTRADA_ID, qtdOp, Convert.ToInt16(idOp));
                    AtualizarQtdReserva_ITEMIDENTIF(qtd_movimento, SKUENTRADA_ID);
                }

            }
            else
            {
                throw new Exception("Não Consta item na Formulação desta OP");
            }

        }

        public void Incluir_ConsumoComponente(String SKU, int qtd, int itemIdentif)
        {
            /*INSERE O CONSUMO DE UM DOS COMPONENTES DA FORMULAÇÃO DA OP*/

            Sql.ExecuteNonQuery(
               " INSERT INTO [EFB_MOVIMENTO]  " +
          "   ([MOVIMENTO_PAI_ID],[ITEMIDENTIF_ID]" +
          "    ,[SKU_ID],[QUANTIDADE],[OPERACAO_ID],[STATUS_MOV],[OP_ID],[DATA_MOV]) " +
          "    VALUES(null,@itemidentif_id,@sku,@qtd_movimento,'Saida',0,null,GETDATE()) "
     , new SqlParameter[] { 
                
                new SqlParameter("@sku", SKU),
                 new SqlParameter("@itemidentif_id", itemIdentif),
                 new SqlParameter("@qtd_movimento", qtd)
            
            });


        }

        public void AtualizarQtdReserva_ITEMIDENTIF(int qtd_movimento, String SKU_ID)
        {

            int id = 0;

            DataTable Table = Sql.ExecuteReader("SELECT ITEMIDENTIF_ID FROM EFB_ITEMIDENTIF WHERE (SKU_ID = @sku) AND (QTD_ORIGINAL - QTD_BAIXADA - QTD_RESERVADA >= @qtd_movimento) ORDER BY QTD_BAIXADA",//consulta todo os item relacionado a op
                new SqlParameter[] { 
                    new SqlParameter("@qtd_movimento", qtd_movimento),
                    new SqlParameter("@sku", SKU_ID) });
            if (Table != null && Table.Rows.Count > 0)
            {
                id = Convert.ToInt16(Table.Rows[0]["ITEMIDENTIF_ID"]);
            }
            else {
                throw new Exception("Não Consta item no estoque para :" + SKU_ID);
            }

            Sql.ExecuteNonQuery(
            "UPDATE [EFB_ITEMIDENTIF] " +
            "SET [QTD_BAIXADA] = [QTD_BAIXADA] + @qtd_movimento  " +
            " WHERE [ITEMIDENTIF_ID] = @id "
       , new SqlParameter[] { 
                new SqlParameter("@qtd_movimento", qtd_movimento),
                new SqlParameter("@id",id )              
            
            });

        }


    }

}


